package com.easy.query.oracle.migration;

import com.easy.query.core.basic.api.database.Credentials;
import com.easy.query.core.configuration.dialect.SQLKeyword;
import com.easy.query.core.migration.AbstractDatabaseMigrationProvider;
import com.easy.query.core.migration.ColumnDbTypeResult;
import com.easy.query.core.migration.MigrationCommand;
import com.easy.query.core.migration.MigrationEntityParser;
import com.easy.query.core.migration.commands.DefaultMigrationCommand;
import com.easy.query.core.migration.data.ColumnMigrationData;
import com.easy.query.core.migration.data.ForeignKeyMigrationData;
import com.easy.query.core.migration.data.IndexMigrationData;
import com.easy.query.core.migration.data.TableMigrationData;
import com.easy.query.core.util.EasyCollectionUtil;
import com.easy.query.core.util.EasyDatabaseUtil;
import com.easy.query.core.util.EasyStringUtil;

import javax.sql.DataSource;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.Map;
import java.util.StringJoiner;
import java.util.function.Function;
import java.util.stream.Collectors;

/**
 * create time 2025/1/19 14:08
 * 文件说明
 *
 * @author xuejiaming
 */
public class OracleDatabaseMigrationProvider extends AbstractDatabaseMigrationProvider {
    public OracleDatabaseMigrationProvider(DataSource dataSource, SQLKeyword sqlKeyword, MigrationEntityParser migrationEntityParser) {
        super(dataSource, sqlKeyword, migrationEntityParser);
    }
    @Override
    public String databaseExistSQL(String databaseName) {
        return String.format("select 1 from sys.dba_users where username='%s'",databaseName);
    }

    @Override
    public void createDatabaseIfNotExists(Function<DataSource, Credentials> jdbcCredentialsByDataSourceFunction) {

    }
    @Override
    public String createDatabaseSQL(String databaseName) {
        throw new UnsupportedOperationException("oracle not support create database command.");
    }

    @Override
    public boolean tableExists(String schema,String tableName) {
        ArrayList<Object> sqlParameters = new ArrayList<>();
        if(EasyStringUtil.isBlank(schema)){
            sqlParameters.add("SYSDBA");
        }else{
            sqlParameters.add(schema);
        }
        sqlParameters.add(tableName);
        List<Map<String, Object>> maps = EasyDatabaseUtil.sqlQuery(dataSource, "select 1 from all_tab_comments where owner=? and table_name=?", sqlParameters);
        return EasyCollectionUtil.isNotEmpty(maps);
    }

    @Override
    public MigrationCommand renameTable(TableMigrationData tableMigrationData) {
        String sql = String.format("execute immediate 'ALTER TABLE %s RENAME TO  %s';", getQuoteSQLName(tableMigrationData.getSchema(),tableMigrationData.getOldTableName()), getQuoteSQLName(tableMigrationData.getSchema(),tableMigrationData.getTableName()));
        return new DefaultMigrationCommand(sql);
    }

    @Override
    public MigrationCommand createTable(TableMigrationData tableMigrationData) {

        StringBuilder sql = new StringBuilder();
        sql.append("BEGIN")
                .append(newLine);
        StringBuilder columnCommentSQL = new StringBuilder();


        String tableComment = getTableComment(tableMigrationData,"''");
        if (EasyStringUtil.isNotBlank(tableComment)) {
            String format = String.format("execute immediate 'COMMENT ON TABLE %s IS %s';", getQuoteSQLName(tableMigrationData.getSchema(),tableMigrationData.getTableName()), tableComment);
            columnCommentSQL.append(newLine)
                    .append(format)
                    .append(newLine);
        }

        sql.append("execute immediate 'CREATE TABLE ").append(getQuoteSQLName(tableMigrationData.getSchema(),tableMigrationData.getTableName())).append(" ( ");
        for (ColumnMigrationData column : tableMigrationData.getColumns()) {
            sql.append(newLine)
                    .append(getQuoteSQLName(column.getName()))
                    .append(" ");
            ColumnDbTypeResult columnDbTypeResult = new ColumnDbTypeResult(column.getDbType(), column.getDefValue());
            sql.append(columnDbTypeResult.columnType);
            if (column.isNotNull()) {
                sql.append(" NOT NULL ");
            } else {
                sql.append(" NULL ");
            }
            if (column.isGeneratedKey()) {
                sql.append(" GENERATED BY DEFAULT AS IDENTITY ");
            }else{
                if (EasyStringUtil.isNotBlank(columnDbTypeResult.defValue)) {
                    sql.append(" DEFAULT ").append(columnDbTypeResult.defValue);
                }
            }
            String columnComment = getColumnComment(column,"''");
            if (EasyStringUtil.isNotBlank(columnComment)) {
                String format = String.format("execute immediate 'COMMENT ON COLUMN %s.%s IS %s';", getQuoteSQLName(tableMigrationData.getSchema(),tableMigrationData.getTableName()), getQuoteSQLName(column.getName()), columnComment);
                columnCommentSQL.append(newLine)
                        .append(format);
            }
            sql.append(",");
        }
        List<ColumnMigrationData> keys = EasyCollectionUtil.filter(tableMigrationData.getColumns(), s -> s.isPrimary());
        if (EasyCollectionUtil.isNotEmpty(keys)) {
            sql.append(newLine)
                    .append(" CONSTRAINT ").append(getQuoteSQLName(getDatabaseName()+"_"+tableMigrationData.getTableName()+"_pk1")).append(" ").append(" PRIMARY KEY (");
            int i = keys.size();
            for (ColumnMigrationData keyColumn : keys) {
                i--;
                sql.append(getQuoteSQLName(keyColumn.getName()));
                if (i > 0) {
                    sql.append(", ");
                } else {
                    sql.append(")");
                }
            }
        }else{
            sql.deleteCharAt(sql.length() - 1);
        }
//        sql.append(newLine).append(")");
        sql.append(newLine).append(") LOGGING ';");
        if(columnCommentSQL.length()>0){
            sql.append(newLine).append(columnCommentSQL);
        }

        sql.append(newLine).append("END;");
        return new DefaultMigrationCommand(sql.toString());
    }

    @Override
    protected MigrationCommand renameColumn(TableMigrationData table, String renameFrom, ColumnMigrationData column) {
        String sql = String.format("execute immediate 'ALTER TABLE %s RENAME COLUMN %s TO  %s';", getQuoteSQLName(table.getSchema(),table.getTableName()), getQuoteSQLName(renameFrom), getQuoteSQLName(column.getName()));
        return new DefaultMigrationCommand(sql);
    }

    @Override
    protected MigrationCommand addColumn(TableMigrationData table, ColumnMigrationData column) {
        StringBuilder sql = new StringBuilder();
        String format = String.format("execute immediate 'ALTER TABLE %s ADD (", getQuoteSQLName(table.getSchema(),table.getTableName()));
        sql.append(format).append(getQuoteSQLName(column.getName()));

        ColumnDbTypeResult columnDbTypeResult = new ColumnDbTypeResult(column.getDbType(), column.getDefValue());
        sql.append(columnDbTypeResult.columnType);
        if (column.isNotNull()) {
            sql.append(" NOT NULL");
        } else {
            sql.append(" NULL");
        }
        if (EasyStringUtil.isNotBlank(columnDbTypeResult.defValue)) {
            sql.append(" DEFAULT ").append(columnDbTypeResult.defValue);
        }
        sql.append(")';");
//
//        String columnComment = getColumnComment(entityMigrationMetadata, column);
//        if (EasyStringUtil.isNotBlank(columnComment)) {
//            sql.append(newLine);
//            sql.append(" COMMENT ON COLUMN ").append(tableName).append(" IS ").append(columnComment);
//            sql.append(";");
//        }
        return new DefaultMigrationCommand(sql.toString());
    }


    @Override
    public MigrationCommand dropTable(TableMigrationData table) {
        return new DefaultMigrationCommand("DROP TABLE " + getQuoteSQLName(table.getSchema(),table.getTableName()) + ";");
    }
    @Override
    protected MigrationCommand createIndex(TableMigrationData table, IndexMigrationData tableIndex) {
        StringBuilder sql = new StringBuilder();
        sql.append("CREATE ");
        if (tableIndex.isUnique()) {
            sql.append("UNIQUE INDEX ");
        } else {
            sql.append("INDEX ");
        }
        sql.append(tableIndex.getIndexName());
        sql.append(" ON ").append(getQuoteSQLName(table.getSchema(), table.getTableName()));
        sql.append(" (");
        StringJoiner joiner = new StringJoiner(",");
        for (int i = 0; i < tableIndex.getFields().size(); i++) {
            IndexMigrationData.EntityField entityField = tableIndex.getFields().get(i);
            String column = getQuoteSQLName(entityField.getColumnName()) + " " + (entityField.isAsc() ? "ASC" : "DESC");
            joiner.add(column);
        }
        sql.append(joiner);
        sql.append(");");
        return new DefaultMigrationCommand(sql.toString());
    }


    @Override
    protected MigrationCommand createTableForeignKey(TableMigrationData table, ForeignKeyMigrationData foreignKey) {
        StringBuilder sql = new StringBuilder();
        sql.append("ALTER TABLE ");
        sql.append(getQuoteSQLName(table.getSchema(), table.getTableName()));
        sql.append(" ADD CONSTRAINT ").append(foreignKey.getName());
        sql.append(" FOREIGN KEY (");

        String selfColumns = Arrays.stream(foreignKey.getSelfColumn()).map(self -> getQuoteSQLName(self)).collect(Collectors.joining(","));
        sql.append(selfColumns);
        sql.append(") REFERENCES ");
        sql.append(getQuoteSQLName(foreignKey.getTargetTable()));
        sql.append(" (");
        String targetColumns = Arrays.stream(foreignKey.getTargetColumn()).map(target -> getQuoteSQLName(target)).collect(Collectors.joining(","));
        sql.append(targetColumns);
        sql.append(")");

        if (EasyStringUtil.isNotBlank(foreignKey.getAction())) {
            sql.append(" ").append(foreignKey.getAction()).append(" ");
        }
        sql.append(";");
        return new DefaultMigrationCommand(sql.toString());
    }
}
